﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using DTOFrameworks;
using System.Data;
using FooFun.DataElements.Dtos;

namespace PhysicalDataProvider
{
    public class DEDataProvider
    {
        public DEDataProvider()
        {
            sqlGenerator = new SqlGenerator();
        }

        SqlGenerator sqlGenerator;

        DbConnection conn;
        DbCommand cmd;

        public void Begin()
        {
            conn = SqlHelper.Connection;
            cmd = conn.CreateCommand();

            conn.Open();
        }

        public void End()
        {
            conn.Close();
        }

        public int Insert(IDataElement de, string[] cols, string table)
        {
            cmd.CommandText = sqlGenerator.InsertSql(cols, table);

            List<DbParameter> list = new List<DbParameter>(cols.Length);

            foreach (string col in cols)
            {
                DbParameter para = cmd.CreateParameter();
                para.ParameterName = sqlGenerator.GetParameterName(col);
                para.Value = de[col].Value;

                list.Add(para);
            }

            cmd.Parameters.Clear();
            cmd.Parameters.AddRange(list.ToArray());

            int val = cmd.ExecuteNonQuery();

            return val;
        }

        public int Insert(object obj, string col, string table)
        {
            cmd.CommandText = sqlGenerator.InsertSql(col, table);

            DbParameter para = cmd.CreateParameter();
            para.ParameterName = sqlGenerator.GetParameterName(col);
            para.Value = obj;

            cmd.Parameters.Clear();
            cmd.Parameters.Add(para);

            int val = cmd.ExecuteNonQuery();

            return val;
        }

        public int Delete(IDataElement de, string idCol, string table)
        {
            cmd.CommandText = sqlGenerator.DeleteSql(idCol, table);

            DbParameter para = cmd.CreateParameter();
            para.ParameterName = sqlGenerator.GetParameterName(idCol);
            para.Value = de[idCol].Value;

            cmd.Parameters.Clear();
            cmd.Parameters.Add(para);

            int val = cmd.ExecuteNonQuery();

            return val;
        }

        public int Delete(object obj, string idCol, string table)
        {
            cmd.CommandText = sqlGenerator.DeleteSql(idCol, table);

            DbParameter para = cmd.CreateParameter();
            para.ParameterName = sqlGenerator.GetParameterName(idCol);
            para.Value = obj;

            cmd.Parameters.Clear();
            cmd.Parameters.Add(para);

            int val = cmd.ExecuteNonQuery();

            return val;
        }

        public int Update
           (IDataElement de, string idCol, string[] cols, string table)
        {
            cmd.CommandText = sqlGenerator.UpdateSql(idCol, cols, table);

            List<DbParameter> list = new List<DbParameter>(cols.Length);

            foreach (string col in cols)
            {
                DbParameter para = cmd.CreateParameter();
                para.ParameterName = sqlGenerator.GetParameterName(col);

                para.Value = de[col].Value;

                list.Add(para);
            }

            cmd.Parameters.Clear();
            cmd.Parameters.AddRange(list.ToArray());

            DbParameter idPara = cmd.CreateParameter();
            idPara.ParameterName = sqlGenerator.GetParameterName(idCol);
            idPara.Value = de[idCol].Value;

            cmd.Parameters.Add(idPara);
            int val = cmd.ExecuteNonQuery();

            return val;
        }

        public IDataElement Select
            (string[] cols, string table, string where)
        {
            string sql = sqlGenerator.SelectSql(cols, table, where);
            cmd.CommandText = sql;

            DbDataReader dr = cmd.ExecuteReader();

            IDataElement de
                = DataReaderEmit.GetDataElement(dr, table);

            dr.Close();

            return de;
        }

        public IDataElement GetScalar
            (object obj, string[] cols, string table, string idCol)
        {
            string sql = sqlGenerator.GetScalarSql(cols, table, idCol);
            cmd.CommandText = sql;
            cmd.Parameters.Clear();

            DbParameter idPara = cmd.CreateParameter();
            idPara.ParameterName = sqlGenerator.GetParameterName(idCol);
            idPara.Value = obj;

            cmd.Parameters.Add(idPara);

            DbDataReader dr = cmd.ExecuteReader();

            IDataElement de
                = DataReaderEmit.GetDataElement(dr, cols, table);

            dr.Close();

            return de;
        }

        public IDataElement GetScalar(string sql, string[] cols, string table)
        {
            cmd.CommandText = sql;
            cmd.Parameters.Clear();

            DbDataReader dr = cmd.ExecuteReader();

            IDataElement de
                = DataReaderEmit.GetDataElement(dr, cols, table);

            dr.Close();

            return de;
        }

        public int ExecuteNonQuery(string sql,
            params DbParameter[] commandParameters)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddRange(commandParameters);
            cmd.CommandText = sql;

            int val = cmd.ExecuteNonQuery();

            return val;
        }

        public DbParameter CreateParameter()
        {
            return cmd.CreateParameter();
        }
    }
}
